#include "DatabaseManager.h"

DatabaseManager::DatabaseManager(QObject *parent) : QObject(parent)
{
    // 初始化 MySQL 连接
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("192.168.0.194");
    db.setPort(3306);
    db.setDatabaseName("text");
    db.setUserName("root");
    db.setPassword("Text@123456");
    // 移除SSL连接选项，使用默认连接方式
    // db.setConnectOptions("CLIENT_SSL=0");
}

DatabaseManager::~DatabaseManager()
{
    if (db.isOpen()) {
        db.close();
    }
}

bool DatabaseManager::connectToDB()
{
    if (!db.open()) {
        qDebug() << "数据库连接失败：" << db.lastError().text();
        return false;
    }
    qDebug() << "数据库连接成功！";
    return true;
}

bool DatabaseManager::initializeUserTable()
{
    QSqlQuery query;
    QString createTableSQL = R"(
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            password_hash VARCHAR(64) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_login_at TIMESTAMP NULL,
            status TINYINT DEFAULT 1
        )
    )";
    
    if (!query.exec(createTableSQL)) {
        qDebug() << "创建用户表失败：" << query.lastError().text();
        return false;
    }
    
    qDebug() << "用户表初始化成功！";
    return true;
}

bool DatabaseManager::initializeRobotTables()
{
    QSqlQuery query;
    
    // 创建 robot 表
    QString createRobotTableSQL = R"(
        CREATE TABLE IF NOT EXISTS robot (
            id BIGINT NOT NULL AUTO_INCREMENT,
            sn VARCHAR(64) NOT NULL COMMENT '机器人序列号',
            ip VARCHAR(45) NOT NULL,
            port INT UNSIGNED NOT NULL,
            online TINYINT(1) NOT NULL DEFAULT 1,
            conn_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_ping DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE INDEX uk_sn (sn)
        ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci
    )";
    
    if (!query.exec(createRobotTableSQL)) {
        qDebug() << "创建机器人表失败：" << query.lastError().text();
        return false;
    }
    
    // 创建 robot_data 表
    QString createRobotDataTableSQL = R"(
        CREATE TABLE IF NOT EXISTS robot_data (
            id BIGINT NOT NULL AUTO_INCREMENT,
            sn VARCHAR(64) NOT NULL,
            rx_time DATETIME(3) NOT NULL,
            pos_x DOUBLE NULL DEFAULT NULL,
            pos_y DOUBLE NULL DEFAULT NULL,
            pos_z DOUBLE NULL DEFAULT NULL,
            temp DOUBLE NULL DEFAULT NULL,
            PRIMARY KEY (id),
            INDEX idx_sn_time (sn, rx_time)
        ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci
    )";
    
    if (!query.exec(createRobotDataTableSQL)) {
        qDebug() << "创建机器人数据表失败：" << query.lastError().text();
        return false;
    }
    
    qDebug() << "机器人表初始化成功！";
    return true;
}

QString DatabaseManager::hashPassword(const QString &password)
{
    QByteArray passwordBytes = password.toUtf8();
    QByteArray hashBytes = QCryptographicHash::hash(passwordBytes, QCryptographicHash::Sha256);
    return QString::fromLatin1(hashBytes.toHex());
}

bool DatabaseManager::registerUser(const QString &username, const QString &email, const QString &password)
{
    // 检查用户名和邮箱是否已存在
    if (userExists(username)) {
        qDebug() << "注册失败：用户名已存在";
        return false;
    }
    
    if (emailExists(email)) {
        qDebug() << "注册失败：邮箱已存在";
        return false;
    }

    // 密码加密
    QString passwordHash = hashPassword(password);

    // 插入用户数据
    QSqlQuery query;
    query.prepare(R"(
        INSERT INTO users (username, email, password_hash)
        VALUES (:username, :email, :password_hash)
    )");

    query.bindValue(":username", username);
    query.bindValue(":email", email);
    query.bindValue(":password_hash", passwordHash);

    if (!query.exec()) {
        qDebug() << "注册失败：" << query.lastError().text();
        return false;
    }
    
    qDebug() << "用户注册成功：" << username;
    return true;
}

QVariantMap DatabaseManager::loginUser(const QString &username, const QString &password)
{
    QVariantMap userInfo;

    // 计算输入密码的哈希值
    QString inputHash = hashPassword(password);

    // 查询数据库（添加 is_admin 字段）
    QSqlQuery query;
    query.prepare(R"(
        SELECT id, username, email, created_at, last_login_at, is_admin
        FROM users
        WHERE username = :username AND password_hash = :password_hash AND status = 1
    )");

    query.bindValue(":username", username);
    query.bindValue(":password_hash", inputHash);

    if (!query.exec()) {
        qDebug() << "登录查询失败：" << query.lastError().text();
        return userInfo;
    }

    if (query.next()) {
        userInfo["id"] = query.value("id").toInt();
        userInfo["username"] = query.value("username").toString();
        userInfo["email"] = query.value("email").toString();
        userInfo["created_at"] = query.value("created_at").toString();
        userInfo["last_login_at"] = query.value("last_login_at").toString();
        userInfo["is_admin"] = query.value("is_admin").toBool();
        
        // 更新最后登录时间
        QSqlQuery updateQuery;
        updateQuery.prepare("UPDATE users SET last_login_at = NOW() WHERE id = :id");
        updateQuery.bindValue(":id", userInfo["id"].toInt());
        updateQuery.exec();
        
        qDebug() << "用户登录成功：" << username << "，管理员权限：" << userInfo["is_admin"].toBool();
    } else {
        qDebug() << "登录失败：用户名或密码错误";
    }

    return userInfo;
}

bool DatabaseManager::userExists(const QString &username)
{
    QSqlQuery query;
    query.prepare("SELECT COUNT(*) FROM users WHERE username = :username");
    query.bindValue(":username", username);
    
    if (query.exec() && query.next()) {
        return query.value(0).toInt() > 0;
    }
    return false;
}

bool DatabaseManager::emailExists(const QString &email)
{
    QSqlQuery query;
    query.prepare("SELECT COUNT(*) FROM users WHERE email = :email");
    query.bindValue(":email", email);
    
    if (query.exec() && query.next()) {
        return query.value(0).toInt() > 0;
    }
    return false;
}

// ==== 机器人管理相关方法 ====

bool DatabaseManager::registerRobot(const QString &sn, const QString &ip, int port)
{
    // 检查IP地址有效性
    if (ip.isEmpty() || ip == ":0") {
        qDebug() << "注册机器人失败：无效的IP地址 SN=" << sn << ", IP=" << ip;
        return false;
    }
    
    QSqlQuery query;
    
    // 检查机器人是否已存在
    query.prepare("SELECT id FROM robot WHERE sn = :sn");
    query.bindValue(":sn", sn);
    
    if (query.exec() && query.next()) {
        // 机器人已存在，更新连接信息
        query.prepare(R"(
            UPDATE robot SET 
                ip = :ip, 
                port = :port, 
                online = 1, 
                conn_time = NOW(), 
                last_ping = NOW() 
            WHERE sn = :sn
        )");
        query.bindValue(":ip", ip);
        query.bindValue(":port", port);
        query.bindValue(":sn", sn);
        
        if (!query.exec()) {
            qDebug() << "更新机器人信息失败：" << query.lastError().text();
            return false;
        }
        
        qDebug() << "机器人重新连接：" << sn;
        return true;
    } else {
        // 新机器人，插入数据库
        query.prepare(R"(
            INSERT INTO robot (sn, ip, port, online, conn_time, last_ping)
            VALUES (:sn, :ip, :port, 1, NOW(), NOW())
        )");
        query.bindValue(":sn", sn);
        query.bindValue(":ip", ip);
        query.bindValue(":port", port);
        
        if (!query.exec()) {
            qDebug() << "注册机器人失败：" << query.lastError().text();
            return false;
        }
        
        qDebug() << "机器人注册成功：" << sn;
        return true;
    }
}

bool DatabaseManager::updateRobotStatus(const QString &sn, bool online)
{
    QSqlQuery query;
    query.prepare("UPDATE robot SET online = :online WHERE sn = :sn");
    query.bindValue(":online", online ? 1 : 0);
    query.bindValue(":sn", sn);
    
    if (!query.exec()) {
        qDebug() << "更新机器人状态失败：" << query.lastError().text();
        return false;
    }
    
    qDebug() << "机器人状态已更新：" << sn << (online ? "在线" : "离线");
    return true;
}

bool DatabaseManager::updateRobotPing(const QString &sn)
{
    QSqlQuery query;
    query.prepare("UPDATE robot SET last_ping = NOW() WHERE sn = :sn");
    query.bindValue(":sn", sn);
    
    if (!query.exec()) {
        qDebug() << "更新机器人心跳时间失败：" << query.lastError().text();
        return false;
    }
    
    return true;
}

QVariantMap DatabaseManager::getRobotInfo(const QString &sn)
{
    QVariantMap robotInfo;
    
    QSqlQuery query;
    query.prepare(R"(
        SELECT id, sn, ip, port, online, conn_time, last_ping
        FROM robot WHERE sn = :sn
    )");
    query.bindValue(":sn", sn);
    
    if (query.exec() && query.next()) {
        robotInfo["id"] = query.value("id").toLongLong();
        robotInfo["sn"] = query.value("sn").toString();
        robotInfo["ip"] = query.value("ip").toString();
        robotInfo["port"] = query.value("port").toInt();
        robotInfo["online"] = query.value("online").toBool();
        robotInfo["conn_time"] = query.value("conn_time").toString();
        robotInfo["last_ping"] = query.value("last_ping").toString();
    }
    
    return robotInfo;
}

QVariantList DatabaseManager::getAllRobots()
{
    QVariantList robotList;
    
    QSqlQuery query;
    query.prepare(R"(
        SELECT id, sn, ip, port, online, conn_time, last_ping
        FROM robot ORDER BY conn_time DESC
    )");
    
    if (query.exec()) {
        while (query.next()) {
            QVariantMap robotInfo;
            robotInfo["id"] = query.value("id").toLongLong();
            robotInfo["sn"] = query.value("sn").toString();
            robotInfo["ip"] = query.value("ip").toString();
            robotInfo["port"] = query.value("port").toInt();
            robotInfo["online"] = query.value("online").toBool();
            robotInfo["conn_time"] = query.value("conn_time").toString();
            robotInfo["last_ping"] = query.value("last_ping").toString();
            robotList.append(robotInfo);
        }
    }
    
    return robotList;
}

QVariantList DatabaseManager::getTimeoutRobots(int timeoutSeconds)
{
    QVariantList timeoutRobots;
    
    QSqlQuery query;
    query.prepare(R"(
        SELECT id, sn, ip, port, online, conn_time, last_ping
        FROM robot 
        WHERE online = 1 AND (
            last_ping IS NULL OR 
            last_ping < DATE_SUB(NOW(), INTERVAL :timeout_seconds SECOND)
        )
        ORDER BY last_ping ASC
    )");
    
    query.bindValue(":timeout_seconds", timeoutSeconds);
    
    if (query.exec()) {
        while (query.next()) {
            QVariantMap robotInfo;
            robotInfo["id"] = query.value("id").toLongLong();
            robotInfo["sn"] = query.value("sn").toString();
            robotInfo["ip"] = query.value("ip").toString();
            robotInfo["port"] = query.value("port").toInt();
            robotInfo["online"] = query.value("online").toBool();
            robotInfo["conn_time"] = query.value("conn_time").toString();
            robotInfo["last_ping"] = query.value("last_ping").toString();
            timeoutRobots.append(robotInfo);
        }
    } else {
        qDebug() << "查询超时机器人失败：" << query.lastError().text();
    }
    
    return timeoutRobots;
}

bool DatabaseManager::insertRobotData(const QString &sn, double posX, double posY, double posZ, double temp)
{
    QSqlQuery query;
    query.prepare(R"(
        INSERT INTO robot_data (sn, rx_time, pos_x, pos_y, pos_z, temp)
        VALUES (:sn, NOW(3), :pos_x, :pos_y, :pos_z, :temp)
    )");
    
    query.bindValue(":sn", sn);
    query.bindValue(":pos_x", posX);
    query.bindValue(":pos_y", posY);
    query.bindValue(":pos_z", posZ);
    query.bindValue(":temp", temp);
    
    if (!query.exec()) {
        qDebug() << "插入机器人数据失败：" << query.lastError().text();
        return false;
    }
    
    return true;
}

bool DatabaseManager::insertFullRobotData(const QString &sn, const QVariantMap &data)
{
    QSqlQuery query;
    
    // 首先检查是否存在具有相同SN的记录
    QSqlQuery checkQuery;
    checkQuery.prepare("SELECT id FROM robot_data WHERE sn = :sn ORDER BY rx_time DESC LIMIT 1");
    checkQuery.bindValue(":sn", sn);
    
    if (!checkQuery.exec()) {
        qDebug() << "检查机器人数据失败：" << checkQuery.lastError().text();
        return false;
    }
    
    // 如果存在具有相同SN的记录，则更新该记录；否则插入新记录
    if (checkQuery.next()) {
        // 更新现有记录
        query.prepare(R"(
            UPDATE robot_data SET
                rx_time = NOW(3),
                pos_x = :pos_x,
                pos_y = :pos_y,
                pos_z = :pos_z,
                temp = :temp,
                battery = :battery,
                status = :status,
                control_permission = :control_permission,
                steering_control = :steering_control,
                motion_control = :motion_control,
                generator_start = :generator_start,
                relay_220v_control = :relay_220v_control,
                water_spray_control = :water_spray_control,
                cooling_control = :cooling_control,
                speed_control = :speed_control,
                cutter_control = :cutter_control,
                generator_voltage = :generator_voltage,
                generator_current = :generator_current,
                generator_power = :generator_power,
                generator_temp = :generator_temp,
                engine_temp = :engine_temp,
                hot_water_temp = :hot_water_temp,
                coolant_temp = :coolant_temp,
                box_temp = :box_temp,
                fuel_level = :fuel_level,
                water_level = :water_level,
                plc_output_status = :plc_output_status,
                status_bits = :status_bits,
                lidar_distance = :lidar_distance,
                lidar_angle = :lidar_angle,
                laser_distance = :laser_distance,
                laser_signal = :laser_signal,
                ir_min_temp = :ir_min_temp,
                ir_max_temp = :ir_max_temp,
                cam_width = :cam_width,
                cam_height = :cam_height
            WHERE id = :id
        )");
        
        // 绑定ID
        query.bindValue(":id", checkQuery.value("id").toLongLong());
    } else {
        // 插入新记录
        query.prepare(R"(
            INSERT INTO robot_data (
                sn, rx_time, pos_x, pos_y, pos_z, temp, battery, status,
                control_permission, steering_control, motion_control, generator_start,
                relay_220v_control, water_spray_control, cooling_control, speed_control, cutter_control,
                generator_voltage, generator_current, generator_power, generator_temp,
                engine_temp, hot_water_temp, coolant_temp, box_temp, fuel_level,
                water_level, plc_output_status, status_bits,
                lidar_distance, lidar_angle, laser_distance, laser_signal,
                ir_min_temp, ir_max_temp, cam_width, cam_height
            ) VALUES (
                :sn, NOW(3), :pos_x, :pos_y, :pos_z, :temp, :battery, :status,
                :control_permission, :steering_control, :motion_control, :generator_start,
                :relay_220v_control, :water_spray_control, :cooling_control, :speed_control, :cutter_control,
                :generator_voltage, :generator_current, :generator_power, :generator_temp,
                :engine_temp, :hot_water_temp, :coolant_temp, :box_temp, :fuel_level,
                :water_level, :plc_output_status, :status_bits,
                :lidar_distance, :lidar_angle, :laser_distance, :laser_signal,
                :ir_min_temp, :ir_max_temp, :cam_width, :cam_height
            )
        )");
        
        // 绑定SN
        query.bindValue(":sn", sn);
    }
    
    // 基本位置和温度数据
    query.bindValue(":pos_x", data.value("pos_x", 0.0).toDouble());
    query.bindValue(":pos_y", data.value("pos_y", 0.0).toDouble());
    query.bindValue(":pos_z", data.value("pos_z", 0.0).toDouble());
    query.bindValue(":temp", data.value("temperature", 0.0).toDouble());
    
    // 电池和状态
    query.bindValue(":battery", data.value("battery", 0.0).toDouble());
    query.bindValue(":status", data.value("status", "unknown").toString());
    
    // PLC控制类寄存器
    QVariantMap plcData = data.value("plc").toMap();
    query.bindValue(":control_permission", plcData.value("control_permission", 0).toInt());
    query.bindValue(":steering_control", plcData.value("steering_control", 0).toInt());
    query.bindValue(":motion_control", plcData.value("motion_control", 0).toInt());
    query.bindValue(":generator_start", plcData.value("generator_start", 0).toInt());
    query.bindValue(":relay_220v_control", plcData.value("relay_220v_control", 0).toInt());
    query.bindValue(":water_spray_control", plcData.value("water_spray_control", 0).toInt());
    query.bindValue(":cooling_control", plcData.value("cooling_control", 0).toInt());
    query.bindValue(":speed_control", plcData.value("speed_control", 0).toInt());
    query.bindValue(":cutter_control", plcData.value("cutter_control", 0).toInt());
    
    // PLC状态类寄存器
    query.bindValue(":generator_voltage", plcData.value("generator_voltage", 0.0).toDouble());
    query.bindValue(":generator_current", plcData.value("generator_current", 0.0).toDouble());
    query.bindValue(":generator_power", plcData.value("generator_power", 0.0).toDouble());
    query.bindValue(":generator_temp", plcData.value("generator_temp", 0.0).toDouble());
    query.bindValue(":engine_temp", plcData.value("engine_temp", 0.0).toDouble());
    query.bindValue(":hot_water_temp", plcData.value("hot_water_temp", 0.0).toDouble());
    query.bindValue(":coolant_temp", plcData.value("coolant_temp", 0.0).toDouble());
    query.bindValue(":box_temp", plcData.value("box_temp", 0.0).toDouble());
    query.bindValue(":fuel_level", plcData.value("fuel_level", 0.0).toDouble());
    query.bindValue(":water_level", plcData.value("water_level", 0.0).toDouble());
    query.bindValue(":plc_output_status", plcData.value("plc_output_status", 0).toInt());
    
    // 状态位（转换为JSON字符串）
    QVariantMap statusBits = plcData.value("status_bits").toMap();
    QJsonObject statusBitsJson;
    for (auto it = statusBits.begin(); it != statusBits.end(); ++it) {
        statusBitsJson.insert(it.key(), QJsonValue::fromVariant(it.value()));
    }
    QJsonDocument statusBitsDoc(statusBitsJson);
    query.bindValue(":status_bits", QString::fromUtf8(statusBitsDoc.toJson(QJsonDocument::Compact)));
    
    // 其他传感器数据
    QVariantMap lidarData = data.value("lidar").toMap();
    query.bindValue(":lidar_distance", lidarData.value("distance", 0.0).toDouble());
    query.bindValue(":lidar_angle", lidarData.value("angle", 0.0).toDouble());
    
    QVariantMap laserData = data.value("laser_range").toMap();
    query.bindValue(":laser_distance", laserData.value("distance", 0.0).toDouble());
    query.bindValue(":laser_signal", laserData.value("signal_strength", 0.0).toDouble());
    
    // 红外温度数据
    query.bindValue(":ir_min_temp", data.value("ir_min_temp", 0.0).toDouble());
    query.bindValue(":ir_max_temp", data.value("ir_max_temp", 0.0).toDouble());
    
    // 相机数据
    QVariantMap cameraData = data.value("camera").toMap();
    query.bindValue(":cam_width", cameraData.value("width", 0).toInt());
    query.bindValue(":cam_height", cameraData.value("height", 0).toInt());
    
    if (!query.exec()) {
        qDebug() << "插入或更新机器人数据失败：" << query.lastError().text();
        return false;
    }
    
    return true;
}

// 更新机器人的连接信息（IP和端口）
bool DatabaseManager::updateRobotConnectionInfo(const QString &sn, const QString &ip, int port)
{
    // 检查IP地址是否有效
    if (ip.isEmpty() || ip == ":0") {
        qDebug() << "无效的IP地址，跳过更新机器人连接信息: SN=" << sn << ", IP=" << ip;
        return false; // 返回false表示更新失败
    }
    
    QSqlQuery query;
    query.prepare("UPDATE robot SET ip = :ip, port = :port WHERE sn = :sn");
    query.bindValue(":ip", ip);
    query.bindValue(":port", port);
    query.bindValue(":sn", sn);
    
    if (!query.exec()) {
        qDebug() << "更新机器人连接信息失败：" << query.lastError().text();
        return false;
    }
    
    return true;
}

// 获取机器人的连接状态
bool DatabaseManager::getRobotConnectionStatus(const QString &sn)
{
    QSqlQuery query;
    query.prepare("SELECT online FROM robot WHERE sn = :sn");
    query.bindValue(":sn", sn);
    
    if (query.exec() && query.next()) {
        return query.value("online").toBool();
    }
    
    return false;
}

// 获取所有活跃的机器人
QVariantList DatabaseManager::getActiveRobots()
{
    QVariantList activeRobots;
    
    QSqlQuery query;
    query.prepare(R"(
        SELECT id, sn, ip, port, online, conn_time, last_ping
        FROM robot 
        WHERE online = 1
        ORDER BY last_ping DESC
    )");
    
    if (query.exec()) {
        while (query.next()) {
            QVariantMap robotInfo;
            robotInfo["id"] = query.value("id").toLongLong();
            robotInfo["sn"] = query.value("sn").toString();
            robotInfo["ip"] = query.value("ip").toString();
            robotInfo["port"] = query.value("port").toInt();
            robotInfo["online"] = query.value("online").toBool();
            robotInfo["conn_time"] = query.value("conn_time").toString();
            robotInfo["last_ping"] = query.value("last_ping").toString();
            activeRobots.append(robotInfo);
        }
    } else {
        qDebug() << "查询活跃机器人失败：" << query.lastError().text();
    }
    
    return activeRobots;
}

// 重置机器人状态（用于设备重新连接时）
bool DatabaseManager::resetRobotStatus(const QString &sn)
{
    QSqlQuery query;
    query.prepare(R"(
        UPDATE robot 
        SET online = 1, conn_time = NOW(), last_ping = NOW()
        WHERE sn = :sn
    )");
    query.bindValue(":sn", sn);
    
    if (!query.exec()) {
        qDebug() << "重置机器人状态失败：" << query.lastError().text();
        return false;
    }
    
    return true;
}

QVariantList DatabaseManager::getRobotData(const QString &sn, int limit)
{
    QVariantList dataList;
    
    QSqlQuery query;
    query.prepare(R"(
        SELECT id, sn, rx_time, pos_x, pos_y, pos_z, temp
        FROM robot_data 
        WHERE sn = :sn 
        ORDER BY rx_time DESC 
        LIMIT :limit
    )");
    
    query.bindValue(":sn", sn);
    query.bindValue(":limit", limit);
    
    if (query.exec()) {
        while (query.next()) {
            QVariantMap dataInfo;
            dataInfo["id"] = query.value("id").toLongLong();
            dataInfo["sn"] = query.value("sn").toString();
            dataInfo["rx_time"] = query.value("rx_time").toString();
            dataInfo["pos_x"] = query.value("pos_x").toDouble();
            dataInfo["pos_y"] = query.value("pos_y").toDouble();
            dataInfo["pos_z"] = query.value("pos_z").toDouble();
            dataInfo["temp"] = query.value("temp").toDouble();
            dataList.append(dataInfo);
        }
    }
    
    return dataList;
}
